package com.ysd.lis.service.impl;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.github.yulichang.toolkit.JoinWrappers;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.ysd.common.Result;
import com.ysd.jdbc.JdbcConnectionManager;
import com.ysd.lis.entity.*;
import com.ysd.lis.mapper.WkCalcElementMapper;
import com.ysd.lis.mapper.WkCalcFormulaMapper;
import com.ysd.lis.mapper.WkCalcResultMapper;
import com.ysd.lis.mapper.sys.SysUserMapper;
import com.ysd.lis.request.BuildResultDataParams;
import com.ysd.lis.request.WkCalcResultSqlDto;
import com.ysd.lis.service.WkCalcElementService;
import com.ysd.lis.service.WkCalcResultService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ysd.lis.service.WkCalcResultSqlService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * <p>
 * 核算结果 服务实现类
 * </p>
 *
 * @author LiuQi
 * @since 2024-04-24
 */
@EnableScheduling
@Service
public class WkCalcResultServiceImpl extends ServiceImpl<WkCalcResultMapper, WkCalcResult> implements WkCalcResultService {

    @Autowired
    WkCalcResultMapper wkCalcResultMapper;

    @Autowired
    SysUserMapper sysUserMapper;

    @Autowired
    WkCalcFormulaMapper wkCalcFormulaMapper;

    @Autowired
    WkCalcElementService wkCalcElementService;

    @Autowired
    WkCalcElementMapper wkCalcElementMapper;

    @Autowired
    WkCalcResultSqlService wkCalcResultSqlService;

    @Override
    public Result getCalcResultList(String batchId) {
        LambdaQueryWrapper<WkCalcResult> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(WkCalcResult::getDelFlag, 0)
                .eq(StrUtil.isNotEmpty(batchId), WkCalcResult::getBatchId, batchId)
                .orderByAsc(WkCalcResult::getFormulaCode);
        List<WkCalcResult> wkCalcResults = wkCalcResultMapper.selectList(lambdaQueryWrapper);
        return Result.succ(1, "查询成功！", wkCalcResults);
    }

    /**
     * 每天凌晨一点执行昨天的要素结果
     */
    @Transactional(rollbackFor = Exception.class)
    @Scheduled(cron = "0 0 1 * * ?")
    public void scheduledBuildSqlData() {
        DateTime yesterday = DateUtil.yesterday();
        DateTime startTime = DateUtil.beginOfDay(yesterday);
        DateTime endTime = DateUtil.endOfDay(yesterday);
        String startFormat = DateUtil.format(startTime, "yyyy-MM-dd: HH:mm:ss");
        String endFormat = DateUtil.format(endTime, "yyyy-MM-dd: HH:mm:ss");
        // 查询所有用户信息（后期修改为所有检验科人员）
        LambdaQueryWrapper<SysUser> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(SysUser::getDelFlag, 0);
        List<SysUser> userList = sysUserMapper.selectList(lambdaQueryWrapper);

        // 查询所有的要素和数据库信息
        MPJLambdaWrapper<WkCalcElement> wkCalcElementMPJLambdaWrapper = JoinWrappers.lambda(WkCalcElement.class)
                .selectAll(WkCalcElement.class)
                .selectAs(WkCalcDatabase::getDatabaseName, WkCalcResultSqlDto::getDatabaseName)
                .selectAs(WkCalcDatabase::getDatabaseCode, WkCalcResultSqlDto::getDatabaseCode)
                .selectAs(WkCalcDatabase::getDatabaseType, WkCalcResultSqlDto::getDatabaseType)
                .selectAs(WkCalcDatabase::getHost, WkCalcResultSqlDto::getHost)
                .selectAs(WkCalcDatabase::getPort, WkCalcResultSqlDto::getPort)
                .selectAs(WkCalcDatabase::getUsername, WkCalcResultSqlDto::getDataUsername)
                .selectAs(WkCalcDatabase::getPassword, WkCalcResultSqlDto::getPassword)
                .selectAs(WkCalcDatabase::getDbName, WkCalcResultSqlDto::getDbName)
                .leftJoin(WkCalcDatabase.class, WkCalcDatabase::getId, WkCalcElement::getDatabaseId);
        List<WkCalcResultSqlDto> wkCalcElements = wkCalcElementMapper.selectJoinList(WkCalcResultSqlDto.class, wkCalcElementMPJLambdaWrapper);
        Map<String, WkCalcResultSqlDto> dataBaseMap = wkCalcElements.stream().collect(Collectors.toMap(WkCalcResultSqlDto::getId, Function.identity()));

        // 查询所有的公式列表
        LambdaQueryWrapper<WkCalcFormula> lambdaQueryWrapper1 = new LambdaQueryWrapper<>();
        lambdaQueryWrapper1.eq(WkCalcFormula::getDelFlag, 0);
        List<WkCalcFormula> wkCalcFormulas = wkCalcFormulaMapper.selectList(lambdaQueryWrapper1);
        List<WkCalcResultSql> resultSqls = new ArrayList<>();
        wkCalcFormulas.stream().forEach(formula -> {
            // 解析公式中的要素id
            String formula1 = formula.getFormula();
            String[] split = formula1.split("#");
            List<String> ids = new ArrayList();
            int count = StrUtil.count(formula1, "#");
            for (int i = 0; i < count; i++) {
                if (i % 2 != 0) {
                    ids.add(split[i]);
                }
            }
            // 遍历所有的用户信息
            userList.stream().forEach(user -> {
                // 查询要素sql信息
                final AtomicInteger index = new AtomicInteger(1);
                ids.stream().forEach(item -> {
                    WkCalcElement byId = wkCalcElementService.getById(item);
                    String calcStatement = byId.getCalcStatement();
                    if (calcStatement.contains("@员工工号")) {
                        calcStatement = StrUtil.replace(calcStatement, "@员工工号", user.getUsername());
                    }
                    if (calcStatement.contains("@统计日期(起)")) {
                        calcStatement = StrUtil.replace(calcStatement, "@统计日期(起)", startFormat);
                    }
                    if (calcStatement.contains("@统计日期(止)")) {
                        calcStatement = StrUtil.replace(calcStatement, "@统计日期(止)", endFormat);
                    }
                    WkCalcResultSql wkCalcResultSql = new WkCalcResultSql();
                    wkCalcResultSql.setElementId(item);
                    wkCalcResultSql.setFormulaId(formula.getId());
                    wkCalcResultSql.setFormulaCode(formula.getCode());
                    wkCalcResultSql.setFormulaName(formula.getName());
                    wkCalcResultSql.setFormulaIndex(index.getAndAdd(1));
                    wkCalcResultSql.setUserId(user.getId());
                    wkCalcResultSql.setUserCode(user.getUsername());
                    wkCalcResultSql.setUserName(user.getRealname());
                    wkCalcResultSql.setFormula(formula.getFormula());
                    wkCalcResultSql.setSqlValue(calcStatement);
                    wkCalcResultSql.setStartDate(startTime.toTimestamp());
                    wkCalcResultSql.setEndDate(endTime.toTimestamp());
                    wkCalcResultSql.setDatabaseName(dataBaseMap.get(item).getDatabaseName());
                    wkCalcResultSql.setDatabaseCode(dataBaseMap.get(item).getDatabaseCode());
                    wkCalcResultSql.setHost(dataBaseMap.get(item).getHost());
                    wkCalcResultSql.setPort(dataBaseMap.get(item).getPort());
                    wkCalcResultSql.setDatabaseType(dataBaseMap.get(item).getDatabaseType());
                    wkCalcResultSql.setDataUsername(dataBaseMap.get(item).getDataUsername());
                    wkCalcResultSql.setPassword(dataBaseMap.get(item).getPassword());
                    wkCalcResultSql.setDatabase(dataBaseMap.get(item).getDbName());
                    resultSqls.add(wkCalcResultSql);
                });
            });
        });
        Map<Long, List<WkCalcResultSql>> dataBaseTypeMap = resultSqls.stream().collect(Collectors.groupingBy(WkCalcResultSql::getDatabaseType));
        dataBaseTypeMap.forEach((key, value) -> {
            JdbcConnectionManager instance = new JdbcConnectionManager();
            WkCalcResultSql wkCalcResultSql = value.get(0);
            DataBaseBo dataBaseBo = new DataBaseBo();
            if (0L == key) {
                dataBaseBo.setDriverClassName("oracle.jdbc.OracleDriver");
                dataBaseBo.setUrl("jdbc:oracle:thin:" + wkCalcResultSql.getHost() + ":" + wkCalcResultSql.getPort() + ":" + "lis");
                dataBaseBo.setUsername(wkCalcResultSql.getDataUsername());
                dataBaseBo.setPassword(wkCalcResultSql.getPassword());
            } else if (1L == key) {
                dataBaseBo.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                dataBaseBo.setUrl("jdbc:sqlserver://" + wkCalcResultSql.getHost() + ":" + wkCalcResultSql.getPort() + ";DatabaseName=" + "ysd_lis_new1");
                dataBaseBo.setUsername(wkCalcResultSql.getDataUsername());
                dataBaseBo.setPassword(wkCalcResultSql.getPassword());
            } else if (2L == key) {
                dataBaseBo.setDriverClassName("dm.jdbc.driver.DmDriver");
                dataBaseBo.setUrl("jdbc:dm://" + wkCalcResultSql.getHost() + ":" + wkCalcResultSql.getPort() + "/YSD_LIS?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8");
                dataBaseBo.setUsername(wkCalcResultSql.getDataUsername());
                dataBaseBo.setPassword(wkCalcResultSql.getPassword());
            } else {
                new RuntimeException("暂不支持数据库类型！！");
            }
            Connection connection = instance.getConnection(dataBaseBo);
            execSql(connection, instance, value);
            // 最后要关闭连接
            instance.closeConnection(connection);
        });
    }

    /**
     * 建立数据库连接  执行sql 结果表插入数据
     *
     * @param instance
     * @param resultSqls
     */
    @Transactional(rollbackFor = Exception.class)
    public void execSql(Connection connection, JdbcConnectionManager instance, List<WkCalcResultSql> resultSqls) {
        List<WkCalcResultSql> collect = resultSqls.stream().map(wkCalcResultSql -> {
            String listSql = wkCalcResultSql.getSqlValue();
            List<Map<String, Object>> list = instance.executeQuery(connection, listSql);
            Map<String, Object> map = list.get(0);
            if (map.containsKey("result")) {
                map.put("RESULT", map.get(map.get("result")));
            }
            Double result = Double.parseDouble(map.get("RESULT").toString());
            wkCalcResultSql.setResult(result);
            return wkCalcResultSql;
        }).collect(Collectors.toList());
        boolean b = wkCalcResultSqlService.saveBatch(collect);
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Result buildData(BuildResultDataParams buildResultDataParams) {
        wkCalcResultSqlService.findCalcResultList(buildResultDataParams);
        return Result.succ(1, "构建成功", "");
    }

    @Override
    public Result deleteCalcResult(List<String> ids) {
        boolean save = this.removeByIds(ids);
        if (save) {
            return Result.succ(1, "删除成功！", save);
        } else {
            return Result.fail("删除失败");
        }
    }
}
